package com.serviceprovider.business.module.report.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.report.model.CustomReport;
import com.serviceprovider.business.module.report.services.dao.interfaces.ICustomReportDao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

//~--- JDK imports ------------------------------------------------------------

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.List;

@Repository
public class CustomReportDaoImpl extends JdbcDaoSupport implements ICustomReportDao {
    private static final String F_DATE_FROM          = "date_from";
    private static final String F_DATE_TO            = "date_to";
    private static final String F_ID                 = "custom_report_id";
    private static final String F_NAME               = "name";
    private static final String F_PAYEE_IDS          = "payee_ids";
    private static final String F_REPORT_TYPE        = "report_type";
    private static final String F_SELECTED_FEES      = "selected_fees";
    private static final String F_TRANSACTION_STATUS = "transaction_status";
    private static final String F_USER_EMAIL         = "email";
    private static final String F_USER_ID            = "user_id";
    private static final String TABLE                = "custom_reports";
    private static final String USER_TABLE           = "user";
    private static String       UPDATE_A_REPORT      = new StringBuffer("update ").append(TABLE).append(" set ").append(
                                                F_NAME).append("=?, ").append(F_DATE_FROM).append("=?, ").append(
                                                F_DATE_TO).append("=?, ").append(F_PAYEE_IDS).append("=?, ").append(
                                                F_TRANSACTION_STATUS).append("=?, ").append(F_REPORT_TYPE).append(
                                                "=?, ").append(F_SELECTED_FEES).append(",").append(F_USER_ID).append(
                                                "=? ").append(" where ").append(F_ID).append("=?").toString();
    private static String SELECT_REPORTS_BY_USER_ID =
        new StringBuffer("select * from ").append(TABLE).append(" where ").append(F_USER_ID).append("=?").toString();
    private static String SELECT_REPORTS_BY_USER_EMAIL = new StringBuffer("select * from ").append(TABLE).append(
                                                             " c, ").append(USER_TABLE).append(" u ").append(
                                                             " where c.").append(F_USER_ID).append("=u.id").append(
                                                             " AND u.").append(F_USER_EMAIL).append("=?").toString();
    private static String SELECT_A_REPORT =
        new StringBuffer("select * from ").append(TABLE).append(" where ").append(F_ID).append("=?").toString();
    private static String SELECT_ALL_REPORTS = new StringBuffer("select * from ").append(TABLE).toString();
    private static String INSERT_A_REPORT    = new StringBuffer("insert into ").append(TABLE).append(" (").append(
                                                F_NAME).append(",").append(F_DATE_FROM).append(",").append(
                                                F_DATE_TO).append(",").append(F_PAYEE_IDS).append(",").append(
                                                F_TRANSACTION_STATUS).append(",").append(F_REPORT_TYPE).append(
                                                ",").append(F_SELECTED_FEES).append(",").append(F_USER_ID).append(
                                                ")").append(" values (?, ?, ?, ?, ?, ?, ?, ?)").toString();
    private static String DELETE_A_REPORT =
        new StringBuffer("delete from ").append(TABLE).append(" where ").append(F_ID).append("=?").toString();

    @Autowired
    public CustomReportDaoImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    /**
     * List custom reports by user by user id
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<CustomReport> listByUser(int userId) {
        return getJdbcTemplate().query(SELECT_REPORTS_BY_USER_ID, new Object[] { userId }, new CustomReportMapper());
    }

    /**
     * List custom reports by user by user email
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<CustomReport> listByUser(String emailId) {
        CustomReportMapper rowMapper = new CustomReportMapper();

        if (emailId != null) {
            return getJdbcTemplate().query(SELECT_REPORTS_BY_USER_EMAIL, new Object[] { emailId }, rowMapper);
        } else {
            return getJdbcTemplate().query(SELECT_ALL_REPORTS, rowMapper);
        }
    }

    @Override
    public void save(CustomReport report) {
        String sql = (report.getId() == null)
                     ? INSERT_A_REPORT
                     : UPDATE_A_REPORT;

        getJdbcTemplate().update(sql, getParams(report));
    }

    private Object[] getParams(CustomReport report) {
        Object[] insertParams = fillParams(report);

        if (report.getId() == null) {
            return insertParams;
        } else {
            Object[] updateParams = new Object[insertParams.length + 1];

            System.arraycopy(insertParams, 0, updateParams, 0, updateParams.length - 1);
            updateParams[updateParams.length - 1] = report.getId();

            return updateParams;
        }
    }

    private Object[] fillParams(CustomReport report) {
        return new Object[] {
            report.getName(), report.getDateFrom(), report.getDateTo(), report.getPayeeIdString(),
            report.getTransactionStatus(), report.getReportType(), report.getSelectedFeesString(), report.getUserId()
        };
    }

    @Override
    public void delete(int reportId) {
        getJdbcTemplate().update(DELETE_A_REPORT, new Object[] { reportId });
    }

    @Override
    public CustomReport get(int id) {
        return (CustomReport) getJdbcTemplate().queryForObject(SELECT_A_REPORT, new Object[] { id },
                new CustomReportMapper());
    }

    private class CustomReportMapper implements RowMapper {
        @Override
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            CustomReport report = new CustomReport();

            report.setId(rs.getInt(F_ID));
            report.setName(rs.getString(F_NAME));
            report.setDateFrom(rs.getDate(F_DATE_FROM));
            report.setDateTo(rs.getDate(F_DATE_TO));
            report.setPayeesIdString(rs.getString(F_PAYEE_IDS));
            report.setTransactionStatus(rs.getString(F_TRANSACTION_STATUS));
            report.setReportType(rs.getString(F_REPORT_TYPE));
            report.setSelectedFees(rs.getString(F_SELECTED_FEES));
            report.setUserId(rs.getInt(F_USER_ID));

            return report;
        }
    }
}



